package com.example.tliasmanagement.mapper;

import com.example.tliasmanagement.dto.StudentListDto;
import com.example.tliasmanagement.entity.Student;
import com.example.tliasmanagement.vo.NameValueVo;
import com.example.tliasmanagement.vo.StudentVo;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

@Mapper
public interface StudentMapper {
    /**
     * 分页查询
     */
    List<Student> page(StudentListDto dto);

    /**
     * 班级查询
     */
    List<StudentVo> select();

    /**
     * 添加学员
     */
    @Select("select * from student where no = #{no}")
    Student selectByNo(String no);

    @Select("select * from student where phone = #{phone}")
    Student selectByPhone(String phone);

    @Select("select * from student where id_card = #{idCard}")
    Student selectByIdCard(String idCard);

    @Insert("insert into student(name,no,gender,phone,id_card,is_college,address,degree,graduation_date,clazz_id,create_time, update_time)" +
            "values (#{name},#{no},#{gender},#{phone},#{idCard},#{isCollege},#{address},#{degree},#{graduationDate},#{clazzId},#{createTime}, #{updateTime})")
    void insert(Student student);

    /**
     * 删除学员
     */

    void deleteById(List<Integer> ids);

    /**
     * 根据ID查询学员
     */
    @Select("select * from student where id = #{id}")
    Student selectById(Integer id);

    /**
     * 修改学员
     */
    @Update("update student set name=#{name},no=#{no},gender=#{gender},phone=#{phone},id_card=#{idCard},is_college=#{isCollege}," +
            "address=#{address},degree=#{degree},graduation_date=#{graduationDate},clazz_id=#{clazzId},create_time=#{createTime}," +
            "update_time=#{updateTime} where id = #{id}")
    void update(Student student);

    /**
     * 违纪处理
     */
    @Update("update student set violation_count= violation_count+1," +
            "violation_score = violation_score+#{score} where id = #{id}")
    void updateScore(@Param("id") Integer id, @Param("score") Integer score);

    /**
     * 统计班级人数信息
     */
    @Select("select case clazz_id " +
            "when 1 then 'JavaEE就业163期'" +
            "when 4 then '前端就业90期' " +
            "when 5 then 'JavaEE就业165期'" +
            "when 6 then 'JavaEE就业166期'" +
            "when 7 then '大数据就业58期'" +
            "when 8 then 'JavaEE就业167期'" +
            "when 9 then 'JavaEE就业114期'" +
            "else '其他' " +
            "end as name, count(*) as value " +
            "from student " +
            "group by clazz_id")
    List<NameValueVo> studentCountData();
    /**
     * 统计班级学历信息
     */
    @Select("select case degree " +
            "when 1 then '初中'" +
            "when 2 then '高中'" +
            "when 3 then '大专'" +
            "when 4 then '本科'" +
            "when 5 then '硕士'" +
            "when 6 then '博士'" +
            "else '其他'" +
            "end as name," +
            "count(*) as value " +
            "from student " +
            "group by degree")
    List<NameValueVo> studentDegreeData();
}
